package com.thinkgem.dingdan.modules.bu.utils;

import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.HttpURLConnection;
import java.net.URL;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFCellUtil;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;

import com.thinkgem.dingdan.modules.bu.entity.BuOrder;

public class ExcelTest_bak_01 {

	public static HSSFWorkbook hssfWorkbook;
	public static HSSFSheet hssfSheet;

	/**
	 * 获取第一个Sheet中的单元格
	 * 
	 * @param rowIndex
	 * @param colIndex
	 * @return
	 */
	public static Cell getCellInSheet(int rowIndex, int colIndex) {
		hssfSheet = hssfWorkbook.getSheetAt(0);
		Row row = hssfSheet.getRow(rowIndex - 1);
		Cell cell = row.getCell(colIndex - 1);
		return cell;
	}

	public static void main(String[] args) {
		try {

			// reportOrderMergeXls(getEntity(), "zz", "yy");
			System.out.println("导出完成");
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public static BuOrder getEntity() {
		BuOrder order = new BuOrder();

		order.setId("0ec85e0f047449fc9bff67cb1b821ac6");
		order.setOrderNo("20180916201759");
		order.setOrderType("内部订单");
		order.setProductNo("1");
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		order.setOrderDate(format.format(new Date()));

		order.setShipDate(new Date());
		order.setProductName("1");
		order.setCrafts("1");
		order.setElectroplate("1");
		order.setBack("1");

		order.setPackages("1");
		order.setFitting("1");
		String picUrl = "http://image.baidu.com/search/detail?ct=503316480&z=0&ipn=d&word=%E5%9B%BE%E7%89%87&hs=0&pn=0&spn=0&di=156082395700&pi=0&rn=1&tn=baiduimagedetail&is=0%2C0&ie=utf-8&oe=utf-8&cl=2&lm=-1&cs=415293130%2C2419074865&os=1556766946%2C250663840&simid=4145280632%2C499508967&adpicid=0&lpn=0&ln=30&fr=ala&fm=&sme=&cg=&bdtype=0&oriquery=&objurl=http%3A%2F%2Fimg.zcool.cn%2Fcommunity%2F0117e2571b8b246ac72538120dd8a4.jpg%401280w_1l_2o_100sh.jpg&fromurl=ippr_z2C%24qAzdH3FAzdH3Fooo_z%26e3Bzv55s_z%26e3Bv54_z%26e3BvgAzdH3Fo56hAzdH3FZMTYoNTMzMDQ%3D_z%26e3Bip4s&gsm=0&islist=&querylist=";
		order.setPicUrl(picUrl);
		order.setMaterial("1");

		order.setQuantity("1");
		order.setSalesModelFee("1");
		order.setSalesPrice("1");
		order.setProduceFee("1");
		order.setProduceModelFee("1");

		order.setSalesTotalFee("1");
		order.setProduceTotalFee("1");
		order.setProfit("1");
		order.setCustomerName("业务员001");
		order.setSalesman("客户001");

		order.setSupplierName("厂商001");
		order.setOrderType("未发货");
		order.setDeliveryName("aaa");
		order.setDeliveryNumber("123");

		return order;
	}

	/**
	 * 导出xls文件(合并单元格)
	 * 
	 * @param order
	 * @param xlsFileName
	 * @param sheetName
	 */
	public static void reportOrderMergeXls(BuOrder order, String xlsFileName, String sheetName,
			HttpServletResponse response, String httpPriffix, String winUserfilesBasedir, String linuxUserfilesBasedir)
			throws Exception {

		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");

		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet(sheetName);// 创建一个sheet-test1
		sheet.autoSizeColumn(0);// 自动设宽
		
		// 设置单元格风格，居中对齐.(通用添加边框)
		HSSFCellStyle styleMl = wb.createCellStyle();
		styleMl.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
		styleMl.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
		styleMl.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
		styleMl.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框

		
		// 设置单元格风格，居中对齐.(标题添加边框)
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框

		// 设置字体
		HSSFFont font = wb.createFont();
		font.setFontName("黑体");
		font.setFontHeightInPoints((short) 18);// 设置字体大小
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
		style.setFont(font);// 要用到的字体格式
		

		// 设置单元格风格，居中对齐.(第二行添加边框)
		HSSFCellStyle wordStyle = wb.createCellStyle();
		wordStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		wordStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		wordStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
		wordStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
		wordStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
		wordStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框

		HSSFFont words = wb.createFont();
		words.setColor(HSSFColor.RED.index);
		words.setFontHeightInPoints((short) 14);
		words.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		wordStyle.setFont(words);
		

		// 设置单元格风格，居中对齐.
		HSSFCellStyle wordStyle02 = wb.createCellStyle();
		wordStyle02.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		wordStyle02.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		wordStyle02.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
		wordStyle02.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
		wordStyle02.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
		wordStyle02.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框

		HSSFFont words02 = wb.createFont();
		words02.setColor(HSSFColor.RED.index);
		words02.setFontHeightInPoints((short) 14);
		words02.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		wordStyle02.setFont(words02);

		// 创建第一行
		HSSFRow row = sheet.createRow(0);
		row.setHeight((short)(25 * 20));
		HSSFCell cell = row.createCell(0);
		cell.setCellValue(order.getSupplierName() + "生产单");
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));
		setRegionStyle(sheet, new CellRangeAddress(0, 0, 0, 8), style);
		
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 9, 10));
		row.createCell(9).setCellValue("");
		
		HSSFRow secondRow = sheet.createRow(1);
		secondRow.setHeight((short) (15 * 20));
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 8));
		HSSFCell twoOne = secondRow.createCell(0);
		twoOne.setCellValue(order.getCrafts());
		setRegionStyle(sheet, new CellRangeAddress(1, 1, 0, 8), wordStyle02);

		HSSFCell oneOne = secondRow.createCell(9);
		oneOne.setCellValue("是否初订:");
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 9, 9));
		setRegionStyle(sheet, new CellRangeAddress(1, 1, 9, 9),style);
		
		HSSFCell oneTwo = secondRow.createCell(10);
		if(order.getStatus().equals("0")){
			oneTwo.setCellValue("初订");
		}else{
			oneTwo.setCellValue("续订");
		}
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 10, 10));
		setRegionStyle(sheet, new CellRangeAddress(1,1,10,10), wordStyle02);
		
		HSSFRow thirdRow = sheet.createRow(2);
		thirdRow.setHeight((short) (15 * 20));
		HSSFCell third = thirdRow.createCell(0);
		third.setCellValue("生产编号:");
		sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 0));
		setRegionStyle(sheet, new CellRangeAddress(2, 2, 0, 0), styleMl);
		
		HSSFCell four = thirdRow.createCell(1);
		four.setCellValue(order.getProductNo());
		sheet.addMergedRegion(new CellRangeAddress(2, 2, 1, 5));
		setRegionStyle(sheet, new CellRangeAddress(2, 2, 1, 5), wordStyle);
		
		HSSFCell five = thirdRow.createCell(5);
		sheet.addMergedRegion(new CellRangeAddress(2, 2, 6, 6));
		five.setCellValue("产品名称:");
		setRegionStyle(sheet, new CellRangeAddress(2, 2, 6, 6), styleMl);
		
		HSSFCell six = thirdRow.createCell(6);
		six.setCellValue(order.getProductName());
		sheet.addMergedRegion(new CellRangeAddress(2, 2, 7, 10));
		setRegionStyle(sheet, new CellRangeAddress(2, 2, 7, 10), wordStyle);
		
		HSSFRow fourRow = sheet.createRow(3);
		fourRow.setHeight((short) (15 * 20));
		HSSFCell fourOne = fourRow.createCell(0);
		fourOne.setCellValue("生产数量:");
		sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 0));
		setRegionStyle(sheet, new CellRangeAddress(3, 3, 0, 0), styleMl);
		
		
		HSSFCell fourTwo = fourRow.createCell(1);
		fourTwo.setCellValue(order.getQuantity());
		sheet.addMergedRegion(new CellRangeAddress(3, 3, 1, 5));
		setRegionStyle(sheet, new CellRangeAddress(3, 3, 1, 5), wordStyle);
		
		
		HSSFCell fourThird = fourRow.createCell(5);
		fourThird.setCellValue("下单日期:");
		sheet.addMergedRegion(new CellRangeAddress(3, 3, 6, 6));
		setRegionStyle(sheet,new CellRangeAddress(3, 3, 6, 6), styleMl);
		if (order.getOrderDate() != null && !("").equals(order.getOrderDate())) {
			HSSFCell fourFour = fourRow.createCell(7);
			fourFour.setCellValue(order.getOrderDate());
			fourFour.setCellStyle(wordStyle);
		}
		sheet.addMergedRegion(new CellRangeAddress(3, 3, 7, 10));
		setRegionStyle(sheet,new CellRangeAddress(3, 3, 7, 10), wordStyle);

		
		HSSFRow fiveRow = sheet.createRow(4);
		fiveRow.setHeight((short) (15 * 20));
		HSSFCell fiveOne = fiveRow.createCell(0);
		fiveOne.setCellValue("产品尺寸:");
		sheet.addMergedRegion(new CellRangeAddress(4, 4, 0, 0));
		setRegionStyle(sheet,new CellRangeAddress(4, 4, 0, 0), styleMl);
		
		HSSFCell fiveTwo = fiveRow.createCell(1);
		fiveTwo.setCellValue(order.getProductSize());
		sheet.addMergedRegion(new CellRangeAddress(4, 4, 1, 3));
		setRegionStyle(sheet,new CellRangeAddress(4, 4, 1, 3), wordStyle);
		
		HSSFCell fiveThree = fiveRow.createCell(4);
		fiveThree.setCellValue("厚度:");
		sheet.addMergedRegion(new CellRangeAddress(4, 4, 4, 4));
		setRegionStyle(sheet,new CellRangeAddress(4, 4, 4, 4), styleMl);
		
		HSSFCell fiveFour = fiveRow.createCell(5);
		fiveFour.setCellValue(order.getThinkness());
		sheet.addMergedRegion(new CellRangeAddress(4, 4, 5, 5));
		setRegionStyle(sheet,new CellRangeAddress(4, 4, 5, 5), wordStyle);
		
		HSSFCell fiveFive = fiveRow.createCell(6);
		fiveFive.setCellValue("出货日期:");
		sheet.addMergedRegion(new CellRangeAddress(4, 4, 6, 6));
		setRegionStyle(sheet,new CellRangeAddress(4, 4, 6, 6), styleMl);
		
		if (order.getShipDate() != null && !order.getShipDate().equals("")) {
			HSSFCell fiveSix = fiveRow.createCell(7);
			fiveSix.setCellValue(format.format(order.getShipDate()));
			sheet.addMergedRegion(new CellRangeAddress(4, 4, 7, 10));
			setRegionStyle(sheet,new CellRangeAddress(4, 4, 7, 10), wordStyle);
		}

		sheet.addMergedRegion(new CellRangeAddress(5, 5, 0, 10));
		setRegionStyle(sheet,new CellRangeAddress(5, 5, 0, 10), styleMl);
		
		
		HSSFRow sixRow = sheet.createRow(6);
		sixRow.setHeight((short)(15 * 20));
		HSSFCell sixOne = sixRow.createCell(0);
		sixOne.setCellValue("材料");
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 0, 0));
		setRegionStyle(sheet,new CellRangeAddress(6, 6, 0, 0), styleMl);
		
		HSSFCell sixTwo = sixRow.createCell(1);
		sixTwo.setCellValue("背模");
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 1, 2));
		setRegionStyle(sheet,new CellRangeAddress(6, 6, 1, 2), styleMl);
		
		HSSFCell sixThree = sixRow.createCell(3);
		sixThree.setCellValue("配件");
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 3, 4));
		setRegionStyle(sheet,new CellRangeAddress(6, 6, 3, 4), styleMl);
		
		HSSFCell sixFour = sixRow.createCell(5);
		sixFour.setCellValue("电镀");
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 5, 7));
		setRegionStyle(sheet,new CellRangeAddress(6, 6, 5, 7), styleMl);
		
		HSSFCell sixFive = sixRow.createCell(8);
		sixFive.setCellValue("部门流程");
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 8, 8));
		setRegionStyle(sheet,new CellRangeAddress(6, 6, 8, 8), styleMl);
		
		HSSFCell sixSix = sixRow.createCell(8);
		sixSix.setCellValue("负责人签字");
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 9, 9));
		setRegionStyle(sheet,new CellRangeAddress(6, 6, 9, 9), styleMl);
		
		HSSFCell sixSeven = sixRow.createCell(10);
		sixSeven.setCellValue("实际完成日期");
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 10, 10));
		setRegionStyle(sheet,new CellRangeAddress(6, 6, 10, 10), styleMl);

		
		HSSFRow sevenRow = sheet.createRow(7);
		sevenRow.setHeight((short) (15 * 20));
		HSSFCell sevenOne = sevenRow.createCell(0);
		sevenOne.setCellValue(order.getMaterial());
		sheet.addMergedRegion(new CellRangeAddress(7, 9, 0, 0));
		setRegionStyle(sheet,new CellRangeAddress(7, 9, 0, 0), wordStyle02);
		
		HSSFCell sevenTwo = sevenRow.createCell(1);
		sevenTwo.setCellValue(order.getBack());
		sheet.addMergedRegion(new CellRangeAddress(7, 9, 1, 2));
		setRegionStyle(sheet,new CellRangeAddress(7, 9, 1, 2), wordStyle02);
		
		HSSFCell sevenThree = sevenRow.createCell(3);
		sevenThree.setCellValue(order.getFitting());
		sheet.addMergedRegion(new CellRangeAddress(7, 9, 3, 4));
		setRegionStyle(sheet,new CellRangeAddress(7, 9, 3, 4), wordStyle02);
		
		HSSFCell sevenFour = sevenRow.createCell(5);
		sevenFour.setCellValue(order.getElectroplate());
		sheet.addMergedRegion(new CellRangeAddress(7, 9, 5, 7));
		setRegionStyle(sheet,new CellRangeAddress(7, 9, 5, 7), wordStyle02);
		
		HSSFCell sevenFive = sevenRow.createCell(8);
		sevenFive.setCellValue("刻模");
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 8, 8));
		setRegionStyle(sheet,new CellRangeAddress(7, 7, 8, 8), styleMl);
		
		HSSFCell sevenSix = sevenRow.createCell(9);
		sevenSix.setCellValue("");
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 9, 9));
		setRegionStyle(sheet,new CellRangeAddress(7, 7, 9, 9), styleMl);
		
		HSSFCell sevenSeven = sevenRow.createCell(10);
		sevenSeven.setCellValue("");
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 10, 10));
		setRegionStyle(sheet,new CellRangeAddress(7, 7, 10, 10), styleMl);

		
		HSSFRow eightRow = sheet.createRow(8);
		eightRow.setHeight((short)(15 * 20));
		HSSFCell eightOne = eightRow.createCell(6);
		eightOne.setCellValue("压胚");
		sheet.addMergedRegion(new CellRangeAddress(8, 8, 8, 8));
		setRegionStyle(sheet,new CellRangeAddress(8, 8, 8, 8), styleMl);
		
		HSSFCell eightTwo = eightRow.createCell(9);
		eightTwo.setCellValue("");
		sheet.addMergedRegion(new CellRangeAddress(8, 8, 9, 9));
		setRegionStyle(sheet,new CellRangeAddress(8, 8, 9, 9), styleMl);
		
		HSSFCell eightThree = eightRow.createCell(10);
		eightThree.setCellValue("");
		sheet.addMergedRegion(new CellRangeAddress(8, 8, 10, 10));
		setRegionStyle(sheet,new CellRangeAddress(8, 8, 10, 10), styleMl);

		
		HSSFRow nineRow = sheet.createRow(9);
		nineRow.setHeight((short)(15 * 20));
		HSSFCell nineOne = nineRow.createCell(8);
		nineOne.setCellValue("焊针");
		sheet.addMergedRegion(new CellRangeAddress(9, 9, 8, 8));
		setRegionStyle(sheet,new CellRangeAddress(9, 9, 8, 8), styleMl);
		
		HSSFCell nineTwo = nineRow.createCell(9);
		nineTwo.setCellValue("");
		sheet.addMergedRegion(new CellRangeAddress(9, 9, 9, 9));
		setRegionStyle(sheet,new CellRangeAddress(9, 9, 9, 9), styleMl);
		
		HSSFCell nineThree = nineRow.createCell(10);
		nineThree.setCellValue("");
		sheet.addMergedRegion(new CellRangeAddress(9, 9, 10, 10));
		setRegionStyle(sheet,new CellRangeAddress(9, 9, 10, 10), styleMl);

		HSSFRow tenRow = sheet.createRow(10);
		tenRow.setHeight((short)(15 * 20));
		HSSFCell tenOne = tenRow.createCell(0);
		tenOne.setCellValue("钻刻");
		sheet.addMergedRegion(new CellRangeAddress(10, 10, 0, 0));
		setRegionStyle(sheet,new CellRangeAddress(10, 10, 0, 0), styleMl);
		
		HSSFCell tenTwo = tenRow.createCell(1);
		tenTwo.setCellValue("树脂");
		sheet.addMergedRegion(new CellRangeAddress(10, 10, 1, 2));
		setRegionStyle(sheet,new CellRangeAddress(10, 10, 1, 2), styleMl);
		
		HSSFCell tenThree = tenRow.createCell(3);
		tenThree.setCellValue("抛光");
		sheet.addMergedRegion(new CellRangeAddress(10, 10, 3, 3));
		setRegionStyle(sheet,new CellRangeAddress(10, 10, 3, 3), styleMl);
		
		HSSFCell tenFour = tenRow.createCell(4);
		tenFour.setCellValue("扫古");
		sheet.addMergedRegion(new CellRangeAddress(10, 10, 4, 4));
		setRegionStyle(sheet,new CellRangeAddress(10, 10, 4, 4), styleMl);
		
		
		HSSFCell tenFive = tenRow.createCell(5);
		tenFive.setCellValue("包装");
		sheet.addMergedRegion(new CellRangeAddress(10, 10, 5, 7));
		setRegionStyle(sheet,new CellRangeAddress(10, 10, 5, 7), styleMl);
		
		
		HSSFCell tenSix = tenRow.createCell(8);
		tenSix.setCellValue("抛光");
		sheet.addMergedRegion(new CellRangeAddress(10, 10, 8, 8));
		setRegionStyle(sheet,new CellRangeAddress(10, 10, 8, 8), styleMl);
		
		HSSFCell tenSeven = tenRow.createCell(9);
		tenSeven.setCellValue("");
		sheet.addMergedRegion(new CellRangeAddress(10, 10, 9, 9));
		setRegionStyle(sheet,new CellRangeAddress(10, 10, 9, 9), styleMl);
		
		HSSFCell tenEight = tenRow.createCell(10);
		tenEight.setCellValue("");
		sheet.addMergedRegion(new CellRangeAddress(10, 10, 10, 10));
		setRegionStyle(sheet,new CellRangeAddress(10, 10, 10, 10), styleMl);
		

		HSSFRow elevenRow = sheet.createRow(11);
		elevenRow.setHeight((short)(15 * 20));
		HSSFCell elevenOne = elevenRow.createCell(0);
		elevenOne.setCellValue("");
		sheet.addMergedRegion(new CellRangeAddress(11, 13, 0, 0));
		setRegionStyle(sheet,new CellRangeAddress(11, 13, 0, 0), styleMl);
		
		HSSFCell elevenTwo = elevenRow.createCell(8);
		elevenTwo.setCellValue("电镀");
		sheet.addMergedRegion(new CellRangeAddress(11, 11, 8, 8));
		setRegionStyle(sheet,new CellRangeAddress(11, 11, 8, 8), styleMl);
		
		HSSFCell elevenThree = elevenRow.createCell(9);
		elevenThree.setCellValue("");
		sheet.addMergedRegion(new CellRangeAddress(11, 11, 9, 9));
		setRegionStyle(sheet,new CellRangeAddress(11, 11, 9, 9), styleMl);
		
		HSSFCell elevenFour = elevenRow.createCell(10);
		elevenFour.setCellValue("");
		sheet.addMergedRegion(new CellRangeAddress(11, 11, 10, 10));
		setRegionStyle(sheet,new CellRangeAddress(11, 11, 10, 10), styleMl);
		
		HSSFCell elevenFive = elevenRow.createCell(0);
		elevenFive.setCellValue("");
		sheet.addMergedRegion(new CellRangeAddress(11, 13, 0, 0));
		setRegionStyle(sheet,new CellRangeAddress(11, 13, 0, 0), wordStyle02);
		
		HSSFCell elevenSix = elevenRow.createCell(1);
		elevenSix.setCellValue("");
		sheet.addMergedRegion(new CellRangeAddress(11, 13, 1, 2));
		setRegionStyle(sheet,new CellRangeAddress(11, 13, 1, 2), wordStyle02);
		
		HSSFCell elevenSeven = elevenRow.createCell(3);
		elevenSeven.setCellValue(order.getPolishing());
		sheet.addMergedRegion(new CellRangeAddress(11, 13, 3, 3));
		setRegionStyle(sheet,new CellRangeAddress(11, 13, 3, 3), wordStyle02);
		
		HSSFCell elevenEight = elevenRow.createCell(4);
		elevenEight.setCellValue("");
		sheet.addMergedRegion(new CellRangeAddress(11, 13, 4, 4));
		setRegionStyle(sheet,new CellRangeAddress(11, 13, 4, 4), wordStyle02);
		
		HSSFCell elevenNine = elevenRow.createCell(5);
		elevenNine.setCellValue(order.getPackages());
		sheet.addMergedRegion(new CellRangeAddress(11, 13, 5, 7));
		setRegionStyle(sheet,new CellRangeAddress(11, 13, 5, 7), wordStyle02);

		
		HSSFRow twelveRow = sheet.createRow(12);
		twelveRow.setHeight((short)(15 * 20));
		HSSFCell twelveOne = twelveRow.createCell(8);
		twelveOne.setCellValue("上色");
		setRegionStyle(sheet,new CellRangeAddress(12, 12, 8, 8), styleMl);
		sheet.addMergedRegion(new CellRangeAddress(12, 12, 8, 8));
		
		HSSFCell twelveTwo = twelveRow.createCell(9);
		twelveTwo.setCellValue("");
		sheet.addMergedRegion(new CellRangeAddress(12, 12, 9, 9));
		setRegionStyle(sheet,new CellRangeAddress(12, 12, 9, 9), styleMl);
		
		HSSFCell twelveThree = twelveRow.createCell(10);
		twelveThree.setCellValue("");
		sheet.addMergedRegion(new CellRangeAddress(12, 12, 10, 10));
		setRegionStyle(sheet,new CellRangeAddress(12, 12, 10, 10), styleMl);

		
		HSSFRow thirteenRow = sheet.createRow(13);
		thirteenRow.setHeight((short)(15 * 20));
		HSSFCell thirteenOne = thirteenRow.createCell(8);
		thirteenOne.setCellValue("包装");
		sheet.addMergedRegion(new CellRangeAddress(13, 13, 8, 8));
		setRegionStyle(sheet,new CellRangeAddress(13, 13, 8, 8), styleMl);
		
		HSSFCell thirteenTwo = thirteenRow.createCell(9);
		thirteenTwo.setCellValue("");
		sheet.addMergedRegion(new CellRangeAddress(13, 13, 9, 9));
		setRegionStyle(sheet,new CellRangeAddress(13, 13, 9, 9), styleMl);
		
		HSSFCell thirteenThree = thirteenRow.createCell(10);
		thirteenThree.setCellValue("");
		sheet.addMergedRegion(new CellRangeAddress(13, 13, 10, 10));
		setRegionStyle(sheet,new CellRangeAddress(13, 13, 10, 10), styleMl);

		
		HSSFRow fourteenRow = sheet.createRow(14);
		fourteenRow.setHeight((short)(15 * 20));
		HSSFCell fourteenOne = fourteenRow.createCell(0);
		fourteenOne.setCellValue("图稿");
		sheet.addMergedRegion(new CellRangeAddress(14, 14, 0, 10));
		setRegionStyle(sheet,new CellRangeAddress(14, 14, 0, 10), styleMl);

		
		HSSFRow fifteenRow = sheet.createRow(15);
		fifteenRow.setHeight((short)(10 * 20));
		sheet.addMergedRegion(new CellRangeAddress(15, 36, 0, 10));
		setRegionStyle(sheet,new CellRangeAddress(15, 36, 0, 10), styleMl);
		HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

		System.out.println("picUrl--------->>>>>>>>>" + order.getPicUrl());
		ByteArrayOutputStream outStream = new ByteArrayOutputStream();
		// 将图片写入流中
		BufferedImage bufferImg = ImageIO.read(getInputStream(httpPriffix + order.getPicUrl()));

		ImageIO.write(bufferImg, "PNG", outStream);
		// 利用HSSFPatriarch将图片写入EXCEL
		HSSFPatriarch patri = sheet.createDrawingPatriarch();
		HSSFClientAnchor anchor = new HSSFClientAnchor(50, 50, 50, 50, (short) 1, 16, (short) 8, 36);
		patri.createPicture(anchor, wb.addPicture(outStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));

		HSSFRow sixteenRow = sheet.createRow(37);
		sixteenRow.setHeight((short)(15 * 20));
		HSSFCell sixteenThree = sixteenRow.createCell(0);
		sixteenThree.setCellValue("备注:");
		sheet.addMergedRegion(new CellRangeAddress(37, 38, 0, 0));
		setRegionStyle(sheet,new CellRangeAddress(37, 38, 0, 0), styleMl);
		
		HSSFCell sixteenFour = sixteenRow.createCell(3);
		sixteenFour.setCellValue(order.getRemarks());
		sheet.addMergedRegion(new CellRangeAddress(37, 38, 3, 10));
		setRegionStyle(sheet,new CellRangeAddress(37, 38, 3, 10), wordStyle);
		

		HSSFRow seventeenRow = sheet.createRow(39);
		seventeenRow.setHeight((short)(15 * 20));
		HSSFCell seventeenOne = seventeenRow.createCell(0);
		seventeenOne.setCellValue("跟单:");
		sheet.addMergedRegion(new CellRangeAddress(39, 39, 0, 0));
		setRegionStyle(sheet,new CellRangeAddress(39, 39, 0, 0), styleMl);
		
		HSSFCell seventeenTwo = seventeenRow.createCell(1);
		seventeenTwo.setCellValue(order.getCreateUser());
		sheet.addMergedRegion(new CellRangeAddress(39, 39, 1, 6));
		setRegionStyle(sheet,new CellRangeAddress(39, 39, 1, 6), styleMl);
		
		HSSFCell seventeenThree = seventeenRow.createCell(7);
		seventeenThree.setCellValue("业务员:");
		sheet.addMergedRegion(new CellRangeAddress(39, 39, 7, 7));
		setRegionStyle(sheet,new CellRangeAddress(39, 39, 7, 7), styleMl);
		
		HSSFCell seventeenFour = seventeenRow.createCell(8);
		seventeenFour.setCellValue(order.getCustomerName());
		sheet.addMergedRegion(new CellRangeAddress(39, 39, 8, 10));
		setRegionStyle(sheet,new CellRangeAddress(39, 39, 8, 10), styleMl);
		
		/**
		 * 1、得到所有的合并单元格 sourceSheet.getNumMergedRegions(); 2、得到某一个合并单元格
		 * CellRangeAddress oldRange=sourceSheet.getMergedRegion(i); 3、起始行
		 * oldRange.getFirstRow(); 4、结束行oldRange.getLastRow();
		 * 5、oldRange.getFirstColumn(); 6、oldRange.getLastColumn();
		 */
		int mergeGegions = sheet.getNumMergedRegions();
		System.out.println("mergeGegions------>>>>" + mergeGegions);
		for (int i = 0; i < mergeGegions; i++) {
			org.apache.poi.ss.util.CellRangeAddress oldRange = sheet.getMergedRegion(i);
			int firstRow = oldRange.getFirstRow();
			int lastRow = oldRange.getLastRow();
			int firstColumn = oldRange.getFirstColumn();
			int lastColumn = oldRange.getLastColumn();
			System.out.println("firstRow------>>>>" + firstRow);
			System.out.println("lastRow------>>>>" + lastRow);
			System.out.println("firstColumn------>>>>" + firstColumn);
			System.out.println("lastColumn------>>>>" + lastColumn);
		}

		try {

			String dir = "";
			boolean flag = isLinux();
			if (flag) {
				dir = linuxUserfilesBasedir;
			} else {
				dir = winUserfilesBasedir;
			}

			String fileName = order.getProductNo() + ".xls";
			FileOutputStream fileOut = new FileOutputStream(fileName);
			wb.write(fileOut);
			fileOut.close();
			System.out.println("输出完成");
			// 页面弹出下载或保存
			response.setContentType("application/x-download");
			response.setHeader("Content-disposition",
					"attachment;filename=" + new String(fileName.getBytes("utf-8"), "iso-8859-1"));
			response.setCharacterEncoding("utf-8");
			OutputStream os = response.getOutputStream();
			wb.write(os);
			os.close();
		} catch (Exception e) {
			System.out.println("文件输出失败!");
			e.printStackTrace();
		}

	}

	private static void setRegionStyle(HSSFSheet sheet, CellRangeAddress region,
            HSSFCellStyle cs) {
		for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
			    HSSFRow row = HSSFCellUtil.getRow(i, sheet);
				HSSFCell cell = null;
				 //循环设置单元格样式            
				for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
					cell = HSSFCellUtil.getCell(row, (short) j);
					cell.setCellStyle(cs);
			}

		}
	}
	
	 /**
     * @param border 边框宽度
     * @param region 合并单元格区域范围
     * @param sheet  
     * @param wb
     */
    public static void setRegionBorder(int border, CellRangeAddress region, Sheet sheet,Workbook wb){  
        RegionUtil.setBorderBottom(border,region, sheet, wb);  
        RegionUtil.setBorderLeft(border,region, sheet, wb);  
        RegionUtil.setBorderRight(border,region, sheet, wb);  
        RegionUtil.setBorderTop(border,region, sheet, wb);  
    }
    
	public static boolean isLinux() {
		return System.getProperty("os.name").toLowerCase().indexOf("linux") >= 0;
	}

	public static InputStream getInputStream(String path) {
		InputStream inputStream = null;
		HttpURLConnection httpURLConnection = null;
		try {
			URL url = new URL(path);
			if (url != null) {
				httpURLConnection = (HttpURLConnection) url.openConnection();
				httpURLConnection.setConnectTimeout(3000);
				httpURLConnection.setRequestMethod("GET");
				int responseCode = httpURLConnection.getResponseCode();
				if (responseCode == 200) {
					inputStream = httpURLConnection.getInputStream();
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return inputStream;
	}

	/**
	 * 判断指定的单元格是否是合并单元格
	 * 
	 * @param sheet
	 * @param row
	 *            行下标
	 * @param column
	 *            列下标
	 * @return
	 */
	public static boolean isMergedRegion(HSSFSheet sheet, int row, int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			org.apache.poi.ss.util.CellRangeAddress range = sheet.getMergedRegion(i);
			int firstColumn = range.getFirstColumn();
			int lastColumn = range.getLastColumn();
			int firstRow = range.getFirstRow();
			int lastRow = range.getLastRow();
			if (row >= firstRow && row <= lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					return true;
				}
			}
		}
		return false;
	}

}
